package yunc.checkData;

public class SqlBox {
   //深度仪数据
    public static  String sqlSdy="insert into hy_base_zjtide \n" +
            "select \n" +
            "\t\ta.\"timestamp\",\n" +
            "\t\ta.stationname,\n" +
            "\t\tto_number(d.temp_enc,'9999.99') temp_enc,\n" +
            "\t\tto_number(d.batt_volt,'9999.99') batt_volt,\n" +
            "\t\tc.cryear,\n" +
            "\t\tc.crday,\n" +
            "\t\tto_number(c.datatimes,'9999.99') datatimes,\n" +
            "\t\ta.\"Depth_m\",\n" +
            "\t\tc.rbrpressureinfo rbrpressureinfo,\n" +
            "\t\tnull \"MachineID\",\n" +
            "\t\tnull \"Depth_m_vrs\",\n" +
            "\t\tnull \"Level_m\",\n" +
            "\t\tnull \"SD_m\",\n" +
            "\t\tc.ums_ts_,\n" +
            "\t\tnow() input_time\n" +
            "from (\n" +
            "    select\n" +
            "\t\t*\n" +
            "\t\tfrom hy_base_zjtide_tmp \n" +
            "\t\twhere to_char(\"timestamp\",'yyyy-MM') = ?\n" +
            "\t\t  and stationname=?\n" +
            ") a \n" +
            "left join hy_tide_station b on a.stationname=b.stationname\n" +
            "left join hy_tide_depthinfo c on a.\"timestamp\"=c.\"timestamp\" and a.stationname = c.stationname\n" +
            "left join (\n" +
            "\t\tselect \n" +
            "\t\t\t\tdistinct \n" +
            "\t\t\t\ta.\"timestamp\",\n" +
            "\t\t\t\ta.batt_volt,\n" +
            "\t\t\t\ta.record,\n" +
            "\t\t\t\ta.stationname,\n" +
            "\t\t\t\ta.temp_enc,\n" +
            "\t\t\t\ta.ums_id_,\n" +
            "\t\t\t\ta.ums_ts_\n" +
            "\t\tfrom hy_tide_csi a\n" +
            ") d on case when to_char(a.\"timestamp\",'mi')>='30' then to_char(a.\"timestamp\",'yyyy-MM-dd hh24:30') else to_char(a.\"timestamp\",'yyyy-MM-dd hh24:00') end = to_char(d.\"timestamp\",'yyyy-MM-dd hh24:mi') and a.stationname = d.stationname\n";

    //雷达数据
    public static String sqlLd="insert into hy_base_zjtide \n" +
            "select \n" +
            "\t\ta.\"timestamp\",\n" +
            "\t\ta.stationname,\n" +
            "\t\tto_number(d.temp_enc, '9999.99') temp_enc,\n" +
            "\t\tto_number(d.batt_volt, '9999.99') batt_volt,\n" +
            "\t\tc.cryear,\n" +
            "\t\tc.crday,\n" +
            "\t\tc.datatimes,\n" +
            "\t\ta.\"Depth_m\",\n" +
            "\t\tnull rbrpressureinfo,\n" +
            "\t\tto_number(c.machineid,'9999') \"MachineID\",\n" +
            "\t\ta.\"Depth_m\" \"Depth_m_vrs\",\n" +
            "\t\tc.level_m \"Level_m\",\n" +
            "\t\tc.sd_m \"SD_m\",\n" +
            "\t\tc.ums_ts_,\n" +
            "\t\tnow() input_time\n" +
            "from (\n" +
            "    select\n" +
            "\t\t*\n" +
            "\t\tfrom hy_base_zjtide_tmp \n" +
            "\t\twhere to_char(\"timestamp\",'yyyy-MM') = ?\n" +
            "\t\t  and stationname=?\n" +
            ") a \n" +
            "left join hy_tide_station b on a.stationname=b.stationname\n" +
            "left join hy_tide_radar c on a.\"timestamp\"=c.\"timestamp\" and a.stationname = c.stationname\n" +
            "left join (\n" +
            "\t\tselect \n" +
            "\t\t\t\tdistinct \n" +
            "\t\t\t\ta.\"timestamp\",\n" +
            "\t\t\t\ta.batt_volt,\n" +
            "\t\t\t\ta.record,\n" +
            "\t\t\t\ta.stationname,\n" +
            "\t\t\t\ta.temp_enc,\n" +
            "\t\t\t\ta.ums_id_,\n" +
            "\t\t\t\ta.ums_ts_\n" +
            "\t\tfrom hy_tide_csi a\n" +
            ") d on case when to_char(a.\"timestamp\",'mi')>='30' then to_char(a.\"timestamp\",'yyyy-MM-dd hh24:30') else to_char(a.\"timestamp\",'yyyy-MM-dd hh24:00') end = to_char(d.\"timestamp\",'yyyy-MM-dd hh24:mi') and a.stationname = d.stationname";

    //查询深度仪的数据
    public static String depthInfoSql="select\n" +
            "\t\t\t\tdistinct \n" +
            "\t\t\t\t\"timestamp\",\n" +
            "\t\t\t\trecord,\n" +
            "\t\t\t\tcryear,\n" +
            "\t\t\t\tcrday,\n" +
            "\t\t\t\tdatatimes,\n" +
            "\t\t\t\tdepth_m,\n" +
            "\t\t\t\trbrpressureinfo,\n" +
            "\t\t\t\tstationname,\n" +
            "\t\t\t\tums_ts_\n" +
            "\t\tfrom hy_tide_depthinfo a \n" +
            "\t\twhere to_char(\"timestamp\",'yyyy-MM') = ?\n" +
            "\t\t\tand stationname=?";

    //查询雷达数据
    public static String radarSql="\t\tselect\n" +
            "\t\t\t\tdistinct \n" +
            "\t\t\t\t\"timestamp\",\n" +
            "\t\t\t\trecord,\n" +
            "\t\t\t\tcryear,\n" +
            "\t\t\t\tcrday,\n" +
            "\t\t\t\tdatatimes,\n" +
            "\t\t\t\tmachineid,\n" +
            "\t\t\t\tdepth_m,\n" +
            "\t\t\t\tlevel_m,\n" +
            "\t\t\t\tsd_m,\n" +
            "\t\t\t\terrorcode,\n" +
            "\t\t\t\tstationname,\n" +
            "\t\t\t\tums_ts_\n" +
            "\t\tfrom hy_tide_radar a \n" +
            "\t\twhere to_char(\"timestamp\",'yyyy-MM') = ?\n" +
            "\t\t\tand stationname=?";

    //查询设备状态数据
    public static String csiSql="select\n" +
            "\t\tdistinct\n" +
            "\t\ta.\"timestamp\",\n" +
            "\t\ta.batt_volt,\n" +
            "\t\ta.record,\n" +
            "\t\ta.stationname,\n" +
            "\t\ta.temp_enc,\n" +
            "\t\ta.ums_ts_\n" +
            "from hy_tide_csi a\n" +
            "where to_char(\"timestamp\",'yyyy-MM') = ?\n" +
            "\tand stationname=?;";
}
